﻿var express = require('express');
var router = express.Router();
var connection = require('../db/connect');
const {
  createToken,
  varifyToken
} = require('../utils/token')
const tokenMiddlWare = require('../utils/checkToken');
const { json } = require('express');
setInterval(function () {
  var sql = `SELECT * FROM good WHERE find_in_set('1',GoodItem)`;
  connection.query(sql, function (err, result) { });
}, 60000)
//-------------------------商城-----------------------------------
// 包含tokenMiddlWare中间件添加token到请求头中
//登陆
router.post('/login', function (req, res, next) {
  var username = req.body.username;
  var password = req.body.password;
  var sql = `SELECT * FROM user WHERE Username='${username}'`;
  connection.query(sql, function (err, result) {
    if (err) {
      res.json({
        status: 500,
        msg: err,
        data: ''
      });
      return;
    }
    if (result.length === 0) {
      res.json({
        status: 0,
        msg: '用户名不存在',
        data: ''
      });
      return;
    }
    if (password === result[0].Password) {
      const token = createToken({ username: username }, 1);
      res.json({
        status: 200,
        msg: 'success',
        data: '',
	username:username,
        token: token

      });
    } else {
      res.json({
        status: 0,
        msg: '密码错误',
        data: ''
      });
    }
  });
});
// 校验token有效性
router.post('/tokenCheck', tokenMiddlWare, function (req, res, next) {
  res.json({
    status: 200,
    msg: 'success',
    data: { ...req.body, token:req.get('token') }
  });

});
//注册
router.post('/reg', function (req, res, next) {
	console.log(req.body)
  var username = req.body.username;
  var password = req.body.password;
  var sql = 'SELECT * FROM user WHERE Username=?';
  var sqlParams = [username];
  connection.query(sql, sqlParams, function (err, result) {
    if (err) {
      res.json({
        status: 500,
        msg: err,
        data: ''
      });
      return;
    }
    if (result.length !== 0) {
      res.json({
        status: 0,
        msg: '用户已存在！',
        data: ''
      });
      return;
    }
    sql = 'INSERT INTO user(Username,Password) VALUES(?,?)';
    sqlParams = [username, password];
    connection.query(sql, sqlParams, function (err, result) {
      if (err) {
        res.json({
          status: 500,
          msg: err,
          data: ''
        });
        return;
      }
      res.json({
        status: 200,
        msg: 'success',
        data: ''
      });
    });
  });
});
// 热销商品
router.get('/hotsale', function (req, res, next) {
  var limit = req.query.limit // 条数限制
  var page = req.query.page // 第几页
  var sql = `SELECT * FROM goods WHERE cat_two_id='热门关键词' limit ${limit * (page - 1)},${limit}`;
  var sql2 = `SELECT * FROM goods WHERE cat_two_id='热门关键词'`;



  connection.query(sql2, function (err, result) {
    connection.query(sql, function (err, result2) {
      if (err) {
        res.json({
          status: 500,
          msg: err,
          data: ''
        });
        return;
      }

      res.json({
        status: 200,
        msg: 'success',
        limit: result2.length,
        data: result2,
        total: result.length,
        totalPage: Math.ceil(result.length / result2.length)
      });

    })

  });
});
// 大牌直售
router.get('/brand', function (req, res, next) {
  var limit = req.query.limit
  var page = req.query.page
  var sql = `SELECT * FROM goods WHERE cat_two_id='品牌墙' limit ${limit * (page - 1)},${limit}`;
  var sql2 = `SELECT * FROM goods WHERE cat_two_id='品牌墙'`;



  connection.query(sql2, function (err, result) {
    connection.query(sql, function (err, result2) {
      if (err) {
        res.json({
          status: 500,
          msg: err,
          data: ''
        });
        return;
      }

      res.json({
        status: 200,
        msg: 'success',
        limit: result2.length,
        data: result2,
        total: result.length,
        totalPage: Math.ceil(result.length / result2.length)
      });

    })

  });
});
// 全部
router.get('/discover', function (req, res, next) {
  var limit = req.query.limit
  var page = req.query.page
  var sql = `SELECT * FROM goods limit ${limit * (page - 1)},${limit}`;
  var sql2 = `SELECT * FROM goods`;



  connection.query(sql2, function (err, result) {
    connection.query(sql, function (err, result2) {
      if (err) {
        res.json({
          status: 500,
          msg: err,
          data: ''
        });
        return;
      }

      res.json({
        status: 200,
        msg: 'success',
        limit: result2.length,
        data: result2,
        total: result.length,
        totalPage: Math.ceil(result.length / result2.length)
      });

    })

  });
});
// 发现导航标题/一级分类
router.get('/find/title', function (req, res, next) {
  var sql = `select cat_one_id,count(cat_one_id) from goods group by cat_one_id`;
  connection.query(sql, function (err, result) {
    if (err) {
      res.json({
        status: 500,
        msg: err,
        data: ''
      });
      return;
    }

    res.json({
      status: 200,
      msg: 'success',
      data: result,
    });

  });
});
// 发现导航商品
router.get('/find/goods', function (req, res, next) {
  var limit = req.query.limit // 条数限制
  var page = req.query.page // 第几页
  var title = req.query.title // 导航标题
  var sql = `SELECT * FROM goods WHERE cat_one_id='${title}' limit ${limit * (page - 1)},${limit}`;
  var sql2 = `SELECT * FROM goods WHERE cat_one_id='${title}'`;



  connection.query(sql2, function (err, result) {
    connection.query(sql, function (err, result2) {
      if (err) {
        res.json({
          status: 500,
          msg: err,
          data: ''
        });
        return;
      }

      res.json({
        status: 200,
        msg: 'success',
        limit: result2.length,
        data: result2,
        total: result.length,
        totalPage: Math.ceil(result.length / result2.length)
      });

    })

  });
});
// 轮播（热门推荐）
router.get('/recommend', function (req, res, next) {
  var sql2 = `SELECT * FROM goods WHERE cat_one_id='热门推荐'`;



  connection.query(sql2, function (err, result) {
    if (err) {
      res.json({
        status: 500,
        msg: err,
        data: ''
      });
      return;
    }

    res.json({
      status: 200,
      msg: 'success',
      data: result,
    });

  });
});
// 商品id获取商品
router.post('/goods/:id', function (req, res, next) {
  var id = req.params.id // 商品id
  var sql2 = `SELECT * FROM goods WHERE goods_id=${id}`;



  connection.query(sql2, function (err, result) {
    if (err) {
      res.json({
        status: 500,
        msg: err,
        data: ''
      });
      return;
    }

    res.json({
      status: 200,
      msg: 'success',
      data: result[0],
    });

  });
});
// 相关商品获取
router.post('/relevant', function (req, res, next) {
  var goodsType = req.body.goodsType;// 商品名称
  var limit = req.body.limit;
  if (req.body.limit) {
    var sql = `SELECT * FROM goods WHERE cat_one_id='${goodsType}' limit 0,${limit}`;
  } else {
    var sql = `SELECT * FROM goods WHERE cat_one_id='${goodsType}'`;
  }



  connection.query(sql, function (err, result) {
    if (err) {
      res.json({
        status: 500,
        msg: err,
        data: ''
      });
      return;
    }

    res.json({
      status: 200,
      msg: 'success',
      data: result,
    });

  });
});
// 根据用户购物车已勾选商品获取商品
router.post('/getGoodById', tokenMiddlWare, function (req, res, next) {
  var goodIds = req.body.goodIds;
  var goodIdStr = '(' + goodIds.toString() + ')';
  var sql = `SELECT * FROM good WHERE Goodid in ${goodIdStr}`;
  connection.query(sql, function (err, result) {
    if (err) {
      res.json({
        status: 500,
        msg: err,
        data: ''
      });
      return;
    }
    res.json({
      status: 200,
      msg: 'success',
      data: result
    });
  });
});
// 关键字搜索
router.get('/search', function (req, res, next) {
  var limit = req.query.limit // 条数限制
  var page = req.query.page // 第几页
  var keyword = req.query.keyword // 关键字
  var sql = `SELECT * FROM goods WHERE cat_one_id like '%${keyword}%' or cat_two_id  like '%${keyword}%' or cat_three_id  like '%${keyword}%' or goods_name  like '%${keyword}%' limit ${limit * (page - 1)},${limit}`;
  var sql2 = `SELECT count(*) FROM goods WHERE cat_one_id like '%${keyword}%' or cat_two_id  like '%${keyword}%' or cat_three_id  like '%${keyword}%' or goods_name  like '%${keyword}%'`;
  connection.query(sql2, function (err1, result1) {
    if (err1) {
      res.json({
        status: 500,
        msg: err1,
        data: ''
      });
      return;
    }
    connection.query(sql, function (err, result2) {
      if (err) {
        res.json({
          status: 500,
          msg: err,
          data: ''
        });
        return;
      }
      res.json({
        status: 200,
        msg: 'success',
        limit: result2.length,
        data: result2,
        total: result1[0]['count(*)'],
        totalPage: Math.ceil(result1[0]['count(*)'] / result2.length)
      });

    });
  })
});
// 获取购物车里商品数量
router.post('/getCartCount', tokenMiddlWare, function (req, res, next) {
  var username = req.body.username; // 用户名
  var sql = `SELECT COUNT(*) as count FROM cart WHERE Username=?`;
  var sqlParams = username;
  connection.query(sql, sqlParams, function (err, result) {
    if (err) {
      res.json({
        status: 500,
        msg: err,
        data: ''
      });
      return;
    }
    res.json({
      status: 200,
      msg: 'success',
      data: result
    });
  });
});
// 添加到购物车
router.post('/addToCart', tokenMiddlWare, function (req, res, next) {
  var username = req.body.username; // 用户名
  var id = req.body.id; // 商品id
  var num = req.body.num; // 商品数量
  var sql = `SELECT Cartcount FROM cart WHERE goods_id=? AND Username=?`;
  var sqlParams = [id, username];
  connection.query(sql, sqlParams, function (err, result) {
    if (err) {
      res.json({
        status: 500,
        msg: err,
        data: ''
      });
      return;
    }
    if (result.length === 0) {
      sql = `INSERT INTO cart VALUES (?, ?, ?,DEFAULT);`;
      sqlParams = [username, id, num];
      connection.query(sql, sqlParams, function (err, res1) {
        if (err) {
          res.json({
            status: 500,
            msg: err,
            data: ''
          });
          return;
        }
        res.json({
          status: 200,
          msg: 'success',
          data: ''
        });
      });
    } else {
      num += result[0].Cartcount;
      sql = `UPDATE cart SET Cartcount=? WHERE Username=? AND goods_id=?`;
      sqlParams = [num, username, id];
      connection.query(sql, sqlParams, function (err, res2) {
        if (err) {
          res.json({
            status: 500,
            msg: err,
            data: ''
          });
          return;
        }
        res.json({
          status: 201,
          msg: 'success',
          data: ''
        });
      });
    }
  });
});
// 获取购物车所有信息
router.post('/getCart', tokenMiddlWare, function (req, res, next) {
  var username = req.body.username; // 用户名
  var sql = `SELECT cart.Cartcount,goods.* FROM cart,goods WHERE cart.goods_id=goods.goods_id AND Username=?`;
  var sql2 = `SELECT checked FROM cart WHERE Username=?`
  var sqlParams = username;
  connection.query(sql, sqlParams, function (err, result) {
    if (err) {
      res.json({
        status: 500,
        msg: err,
        data: ''
      });
      return;
    }
    connection.query(sql2, sqlParams, function (err2, result2) {
      result.forEach(item => {
        result2.forEach(item2 => {
          item.is_select= item2.checked;
        })
      })
      res.json({
        status: 200,
        msg: 'success',
        data: result,
      });
    })
  });
});
// 更新购物车商品数目
router.post('/updateCartCount', tokenMiddlWare, function (req, res, next) {
  var username = req.body.username; // 用户名
  var id = req.body.id; // 商品id
  var num = req.body.num; // 改变数量
  var sql = `UPDATE cart SET Cartcount=? WHERE Username=? AND goods_id=?`;
  var sqlParams = [num, username, id];
  connection.query(sql, sqlParams, function (err, result) {
    if (err) {
      res.json({
        status: 500,
        msg: err,
        data: ''
      });
      return;
    }
    res.json({
      status: 200,
      msg: 'success',
      data: ''
    });
  });
});
// 删除购物车商品
router.post('/delFromCart', tokenMiddlWare, function (req, res, next) {
  var username = req.body.username; // 用户名
  var delIds = req.body.delIds; // 商品id 数组
  var str = delIds.join(',');
  var sql = `DELETE FROM cart WHERE Username=? AND goods_id in (${str})`;
  var sqlParams = [username];
  connection.query(sql, sqlParams, function (err, result) {
    if (err) {
      res.json({
        status: 500,
        msg: err,
        data: ''
      });
      return;
    }
    res.json({
      status: 200,
      msg: 'success',
      data: ''
    });
  });
});
// 更改购物车商品的选中状态
router.post('/updateCartChecked', tokenMiddlWare, function (req, res, next) {
  var username = req.body.username; // 用户名
  var id = req.body.id; // 商品id
  var Checked = req.body.Checked;
  console.log(req.body);
  if (id === "all") {
    var sql = `UPDATE cart SET checked='${Checked}' WHERE Username=?`;
    var sqlParams = [username];
    connection.query(sql, sqlParams, function (err, result) {
      if (err) {
        res.json({
          status: 500,
          msg: err,
          data: ''
        });
        return;
      }
      res.json({
        status: 200,
        msg: 'success',
        data: ''
      });
    });
  } else {
    var sql = `UPDATE cart SET checked='${Checked}' WHERE Username=? AND goods_id=?`;
    var sqlParams = [username, id];
    connection.query(sql, sqlParams, function (err, result) {
      if (err) {
        res.json({
          status: 500,
          msg: err,
          data: ''
        });
        return;
      }
      res.json({
        status: 200,
        msg: 'success',
        data: ''
      });
    });
  }
});
// 获取收货地址
router.post('/getAddress', tokenMiddlWare, function (req, res, next) {
  var username = req.body.username; // 用户名
  var sql = `SELECT * FROM address WHERE Username=?`;
  var sqlParams = [username];
  connection.query(sql, sqlParams, function (err, result) {
    if (err) {
      res.json({
        status: 500,
        msg: err,
        data: ''
      });
      return;
    }
    res.json({
      status: 200,
      msg: 'success',
      data: result
    });
  });
});
// 添加收货地址
router.post('/addAddress', tokenMiddlWare, function (req, res, next) {
    var username = req.body.username; // 用户名
    var name = req.body.name; // 姓名
    var phonenum = req.body.phonenum // 电话
    var address = req.body.address; // 地址
    var addressDetail = req.body.addressDetail; //  详细地址
    var postalCode = req.body.postalCode; //  邮编
    var isDefault = req.body.isDefault; //  是否默认
    var Addressid = req.body.Addressid;
    //---->update_isDefault
    if (isDefault) {
        var sql1 = `UPDATE address SET Isdefault = '0' WHERE Username='${username}'`;
        connection.query(sql1, function (err1, result) {
            if (err1) {
                res.json({
                    status: 500,
                    msg: err1,
                    data: '改变默认地址失败'
                });
                return;
            }
        });
    }
    //--->isDefault_End
    // 查询是否已存在地址
    const updateSql = `SELECT * FROM address WHERE Addressid = ?`
    const params = Addressid;
    connection.query(updateSql, params, function (err, result) {
        if (err) {
            res.json({
                status: 500,
                msg: err,
                data: '查询是否为已存地址失败'
            })
            return
        }
        if (result.length != 0) {
            const updatesql2 = `UPDATE address SET Name = '${name}', Phonenum = '${phonenum}', Address = '${address}', AddressDetail= '${addressDetail}',PostalCode='${postalCode}',IsDefault='${isDefault}' WHERE Addressid = '${Addressid}'`;
            connection.query(updatesql2, function (err1, result) {
                if (err1) {
                    res.json({
                        status: 500,
                        msg: err1,
                        data: ''
                    });
                    return;
                }
                res.json({
                    status: 200,
                    msg: 'success',
                    data: ''
                });
            });
        } else {
            var sql = `INSERT INTO address(Username,Addressid,Name,Phonenum,Address,AddressDetail,PostalCode,Isdefault) VALUES(?,NULL,?,?,?,?,?,?)`;
            var sqlParams = [username, name, phonenum, address, addressDetail, postalCode, isDefault];
            connection.query(sql, sqlParams, function (err, result) {
                if (err) {
                    res.json({
                        status: 500,
                        msg: err,
                        data: ''
                    });
                    return;
                }
                res.json({
                    status: 200,
                    msg: 'success',
                    data: ''
                });
            });
        }
    })
});
// 删除收货地址
router.post('/delAddress', tokenMiddlWare, function (req, res, next) {
  var username = req.body.username; // 用户名
  var addressid = req.body.addressid; // 地址id
  var sql = `DELETE FROM address WHERE Username=? AND Addressid in (${addressid})`;
  var sqlParams = [username];
  connection.query(sql, sqlParams, function (err, result) {
    if (err) {
      res.json({
        status: 500,
        msg: err,
        data: ''
      });
      return;
    }
    res.json({
      status: 200,
      msg: 'success',
      data: ''
    });
  });
});
//根据用户名查询用户订单 传参 username用户名 返回值 用户订单数据（地址、商品、价格等）
router.get('/getOrder', function (req, res, next) {
  var username = req.query.username;
  var limit = req.query.limit
  var page = req.query.page
  var sql2 = `select a.*,
  d.*,
  c.*
  from userorder a
  left join address d on d.addressid=a.addressid
  left join usersuborder b on b.mainorderid=a.orderid
  left join goods c on c.goods_id=b.goodid
  where a.username='${username}'`;

  var sql = `select a.*,d.*,c.*from userorder a left join address d on d.addressid=a.addressid left join usersuborder b on b.mainorderid=a.orderid left join goods c on c.goods_id=b.goodid where a.username=? LIMIT ?`
  var sqlParams = [username, limit * page];

  connection.query(sql2, function (err, result2) {
    connection.query(sql, sqlParams, function (err, result) {
      if (err) {
        res.json({
          status: 500,
          msg: err,
          data: ''
        });
        return;
      }
      res.json({
        status: 200,
        msg: 'success',
        data: result,
        total: result2.length,
        limit: limit
      });
    });
  });
});
// 生成订单
router.post('/createOrder',tokenMiddlWare, function (req, res, next) {
  var username = req.body.username;
  var addressId = req.body.addressId;
  var goods = req.body.goods;
  var totalMoney = req.body.totalMoney;
  sql = 'INSERT INTO userorder(Username,Addressid,Status,totalMoney) VALUES(?,?,1,?)';
  var sqlParams = [username, addressId, totalMoney];
  console.log(req.body);
  connection.query(sql, sqlParams, function (err, result) {
    if (err) {
      res.json({
        status: 500,
        msg: err,
        data: ''
      });
      return;
    }
    var arr = [];
    for (var i = 0; i < goods.length; i++) {
      arr.push([result.insertId, goods[i].GoodId, goods[i].Cartcount]);
    }
    sql = 'INSERT INTO usersuborder(MainOrderId,GoodId,Count) VALUES ?';
    sqlParams = arr;
    console.log(arr);
    connection.query(sql,[sqlParams], function (err, result) {
      if (err) {
        res.json({
          status: 500,
          msg: err,
          data: ''
        });
        return;
      }
      res.json({
        status: 200,
        msg: 'success',
        data: result
      });
    });
  });
});


//-------------------------后台管理-----------------------------------

//登陆
router.post('/managelogin', function (req, res, next) {
  var username = req.body.username;
  var password = req.body.password;
  var sql = `SELECT * FROM manageuser WHERE username='${username}'`;
  connection.query(sql, function (err, result) {
    if (err) {
      res.json({
        status: 500,
        msg: err,
        data: ''
      });
      return;
    }
    if (result.length === 0) {
      res.json({
        status: 0,
        msg: '用户名不存在',
        data: ''
      });
      return;
    }
    console.log(result);
    if (password === result[0].password) {
      const token = createToken({ username: username }, 1);
      res.json({
        status: 200,
        msg: 'success',
        data: '',
	      username:username,
        token: token

      });
    } else {
      res.json({
        status: 0,
        msg: '密码错误',
        data: ''
      });
    }
  });
});

//查询当前管理员用户信息 传参：username   返回值：m默认地址信息 电话 密码 
router.get('/manageuser', function (req, res, next) {
  var username = req.query.username;
  var sql = `SELECT * FROM manageuser WHERE username ='${username}'`;

  connection.query(sql, function (err, result) {
    if (err) {
      res.json({
        status: 500,
        msg: err,
        data: ''
      });
      return;
    }
    res.json({
      status: 200,
      msg: 'success',
      data: result,
    });
  });
});

//查询所有用户信息 传参：limit page   返回值：状态码 页数 总数 当前页数 data查询结果
router.get('/user', function (req, res, next) {
  var limit = req.query.limit
  var page = req.query.page
  var sql = `SELECT * FROM user limit ${limit * (page - 1)},${limit}`;
  var sql2 = `SELECT * FROM user`;
  connection.query(sql2, function (err, result) {
    connection.query(sql, function (err, result2) {
      if (err) {
        res.json({
          status: 500,
          msg: err,
          data: ''
        });
        return;
      }

      res.json({
        status: 200,
        msg: 'success',
        limit: result2.length,
        data: result2,
        total: result.length,
        totalPage: Math.ceil(result.length / result2.length)
      });

    })

  });
});

//修改用户信息 传参：用户名 密码/新用户名/密码+用户名 返回值：修改成功
router.post('/updateUser', function (req, res, next) {
  // var  { username, password }=req.body
  var username = req.body.username; // 用户名
  var newusername = req.body.newusername; // 密码
  var password = req.body.password; // 密码
  if (newusername&&!password) {//只修改用户名
    var sql = `UPDATE user SET Username=? WHERE Username=?`;
    var sqlParams = [newusername, username];
  } else if (!newusername&&password) {//只修改密码
    var sql = `UPDATE user SET Password=? WHERE Username=?`;
    var sqlParams = [password, username];
  } else if (newusername && password) {//修改密码和用户名
    var sql = `UPDATE user SET Password=?,Username=? WHERE Username=?`;
    var sqlParams = [password, newusername, username];
  }
  connection.query(sql, sqlParams, function (err, result) {
    if (err) {
      res.json({
        status: 500,
        msg: err,
        data: ''
      });
      return;
    }
    res.json({
      status: 200,
      msg: 'success',
      data: ''
    });
  });
});

//删除用户信息 传参：用户名（数组） 返回值 修改成功
router.post('/delUser', function (req, res, next) {
  var delUsername = req.body.username; // 商品id 数组
  var sql = `DELETE FROM user WHERE  Username in (?)`;
  var sqlParams=[delUsername]
  connection.query(sql, sqlParams, function (err, result) {
    console.log(sql)
    if (err) {
      res.json({
        status: 500,
        msg: err,
        data: ''
      });
      return;
    }
    res.json({
      status: 200,
      msg: 'success',
      data: '删除成功'
    });
  });
});

//添加管理员 传参：注册的信息 
router.post('/regmanage', function (req, res, next) {
  console.log(req.body)
  var username = req.body.username;
  var password = req.body.password;
  var realname=req.body.realName;
  var age=req.body.age;
  var position=req.body.position;
  var phone=req.body.phone
  var sql = 'SELECT * FROM manageuser WHERE username=?';
  var sqlParams = [username];
  connection.query(sql, sqlParams, function (err, result) {
    if (err) {
      res.json({
        status: 500,
        msg: err,
        data: ''
      });
      return;
    }
    if (result.length !== 0) {
      res.json({
        status: 0,
        msg: '用户已存在！',
        data: ''
      });
      return;
    }
    sql = 'INSERT INTO manageuser(username,password,relname,age,position,phone) VALUES(?,?,?,?,?,?)';
    sqlParams = [username, password,realname,age,position,phone];
    connection.query(sql, sqlParams, function (err, result) {
      if (err) {
        res.json({
          status: 500,
          msg: err,
          data: ''
        });
        return;
      }
      res.json({
        status: 200,
        msg: 'success',
        data: ''
      });
    });
  });
});

//查询商品信息 传参：limit page   返回值：状态码 页数 总数 当前页数 data查询结果
router.get('/getAllgoods', function (req, res, next) {
  var limit = req.query.limit
  var page = req.query.page
  var sql = `SELECT * FROM goods limit ${limit * (page - 1)},${limit}`;
  var sql2 = ` SELECT * FROM goods`;
  connection.query(sql2, function (err, result) {
    connection.query(sql, function (err, result2) {
      if (err) {
        res.json({
          status: 500,
          msg: err,
          data: ''
        });
        return;
      }

      res.json({
        status: 200,
        msg: 'success',
        limit: result2.length,
        data: result2,
        total: result.length,
        totalPage: Math.ceil(result.length / result2.length)
      });

    })

  });
});
//商品分级
router.get('/category', function(req, res, next) {
  var level = req.query.classLevel;
  var sql = `select cat_${level}_id,count(cat_${level}_id) from goods group by cat_${level}_id`;  
  console.log(sql);
  connection.query(sql, function(err, result) {
      if (err) {
          res.json({
              status: 500,
              msg: err,
              data: ''
          });
          return;
      }

      res.json({
          status: 200,
          msg: 'success',
          data: result,
      });

  });
});
router.post('/delshop', function (req, res, next) {
  var goods_ids = req.body.goods_id; // 商品id
  goods_ids.forEach(item =>{
    var sql = `DELETE FROM goods WHERE goods_id= '${item}'`;
    connection.query(sql, function (err, result) {
      if (err) {
        res.json({
          status: 500,
          msg: err,
          data: ''
        });
        return;
      }
    });
  })
  res.json({
    status: 200,
    msg: 'success',
    data: '删除成功',
  });
});
//修改商品信息 
router.post('/updateGoods', function(req, res, next) {
  console.log(req.body);
  var goods_name = req.body.goods_name; // 商品名称
  var goods_price = req.body.goods_price; // 商品价格
  var goods_number = req.body.goods_number; // 商品数量
  var goods_weight = req.body.goods_weight; // 商品重量
  // var cat_id = req.body.cat_id; // 类型id
  // var goods_introduce = req.body.goods_introduce; // 商品详情
  // var goods_big_logo = req.body.goods_big_logo; // 图片logo大图
  // var goods_small_logo = req.body.goods_small_logo; // 图片logo小图
  // var is_select = req.body.is_select; // 是否选中
  // var add_time = req.body.add_time; // 添加商品时间
  var upd_time = req.body.upd_time; // 修改时间
  // var delete_time = req.body.delete_time; // 删除时间
  var cat_one_id = req.body.cat_one_id; // 一级分类id
  var cat_two_id = req.body.cat_two_id; // 二级分类id
  var cat_three_id = req.body.cat_three_id; // 三级分类id
  // var cart_number = req.body.cart_number; // 热卖数量
  var is_promote = req.body.is_promote; // 是否促销
  var goods_state = req.body.goods_state; // 商品状态
  var goods_id = req.body.goods_id; // 商品id
  var sql = ` UPDATE goods SET goods_name =?, goods_price =?, goods_number =?, goods_weight =?,upd_time = ?, cat_one_id = ?, cat_two_id = ?, cat_three_id = ?, is_promote = ?, goods_state =?  WHERE goods_id=?`
      // var sql = `UPDATE cart SET Cartcount=? WHERE Username=? AND goods_id=?`;
  var sqlParams = [goods_name, goods_price, goods_number, goods_weight, upd_time, cat_one_id, cat_two_id, cat_three_id, is_promote, goods_state, goods_id];
  connection.query(sql, sqlParams, function(err, result) {
      if (err) {
        console.log(err);
          res.json({
              status: 500,
              msg: err,
              data: ''
          });
          return;
      }
      res.json({
          status: 200,
          msg: 'success',
          data: ''
      });
  });
});
//删除订单信息
router.post('/delOrder', function (req, res, next) {
  var order_ids = req.body.order_id; // 商品id
  order_ids.forEach(item =>{
    var sql = `DELETE FROM userorder WHERE Orderid= '${item}'`;
    connection.query(sql, function (err, result) {
      if (err) {
        res.json({
          status: 500,
          msg: err,
          data: ''
        });
        return;
      }
    });
  })
  res.json({
    status: 200,
    msg: 'success',
    data: '删除成功',
  });
});
//查询订单
router.get('/getAllOrder', function (req, res, next) {
  var limit = req.query.limit
  var page = req.query.page
  var sql2 = `select a.*,
  b.*,
d.*,
c.*
from userorder a
left join address d on d.addressid=a.addressid
left join usersuborder b on b.mainorderid=a.orderid
left join goods c on c.goods_id=b.goodid`;

  var sql = `select a.*,d.*,b.*,c.*from userorder a left join address d on d.addressid=a.addressid left join usersuborder b on b.mainorderid=a.orderid left join goods c on c.goods_id=b.goodid LIMIT ?`
  var sqlParams = [limit * page];

  connection.query(sql2, function (err, result2) {
      connection.query(sql, sqlParams, function (err, result) {
          if (err) {
              res.json({
                  status: 500,
                  msg: err,
                  data: ''
              });
              return;
          }
          res.json({
              status: 200,
              msg: 'success',
              data: result,
              total: result2.length,
              limit: limit
          });
      });
  });
});
//修改订单状态
router.post('/updateOrderStatus',function(req,res,next){
  const orderArr = req.body.orderArr
  orderArr.forEach(item =>{
    const sql = `UPDATE userorder SET Status = '${item.status}' WHERE Orderid = '${item.orderid}'`
    connection.query(sql, function (err, result) {
      if (err) {
        res.json({
          status: 500,
          msg: err,
          data: '更改订单状态失败'
        });
        return;
      }
    });
  })
  res.json({
    status: 200,
    msg: 'success',
    data: '更改成功',
  });
})
module.exports = router;
